/*
 * 轻落华有限公司 拥有本软件版权 2021 并保留所有权利。
 * Copyright 2021, QingLuoHua,
 * All rights reserved.
 */
package com.qlh.dao;

import cn.hutool.crypto.SecureUtil;
import com.qlh.domain.User;
import com.qlh.util.DbUtilHikari;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 对用户表的操作，注册用户、删除用户、查询用户、修改用户
 * @author li guihong
 * @version 1.0
 * @date 2022/12/31
 */
public class UserDao {
    /**
     * 查询所有用户
     * @return 包含所有对象的list集合
     */
    public List<User> selectAllUser(){
        //创建一个空集合，准备放对象
        List<User> userList = new ArrayList<User>();

        Connection conn= null;
        PreparedStatement pstmt= null;
        ResultSet rs= null;

        conn = DbUtilHikari.getConnection();
        String sql = "select userid,username,userpwd from users";
        try {
            pstmt= conn.prepareStatement(sql);

            rs= pstmt.executeQuery();

            while (rs.next()){
                User user= new User();
                user.setUserid(rs.getInt("userid"));
                user.setUsername(rs.getString("username"));
                user.setUserpwd(rs.getString("userpwd"));
                userList.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtilHikari.close(rs,pstmt,conn);
        }


        return userList;
    }
    /**
     *
     * @param userid
     * @param uname
     * @param pwd
     * @return 修改成功返回1，否则返回0
     */
    public int updateUser(int userid,String uname,String pwd){
    int flag= 0;
    Connection conn= null;
    PreparedStatement pstmt= null;

        conn = DbUtilHikari.getConnection();
        String sql = "update users set username=?,userpwd=? where userid=?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,uname);
            pstmt .setString(2,SecureUtil.md5(pwd));
            pstmt.setInt(3,userid);

            flag= pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtilHikari.close(pstmt,conn);
        }
        return flag;
}
    /**
     * 按照用户编号删除用户
     * @param userid 用户编号
     * @return 删除返回1，否则返回0
     */
    public int deleteUser(int userid){
        int flag= 0;
        //conn表示数据库连接
        Connection conn= null;
        //pstmt是执行SQL语句的
        PreparedStatement pstmt= null;
        //获取数据库连接
        conn = DbUtilHikari.getConnection();
        //定义准备SQl语句
        String sql = " delete from users where userid=?";
        try {
            pstmt = conn.prepareStatement(sql);

            pstmt.setInt(1,userid);

            flag= pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtilHikari.close(pstmt,conn);
        }

        return flag;
    }
    /**
     *对登录用户合法性的检测
     * @param uname 用户名
     * @param pwd 密码
     * @return
     */
    public boolean login(String uname,String pwd){
        //flag 是登陆成功的标志，成功则为true;
        boolean flag= false;
        //conn表示数据库连接
        Connection conn= null;
        //pstmt是执行SQL语句的
        PreparedStatement pstmt= null;
        //rs代表结果集，数据库里查询的结果放置在rs里
        ResultSet rs= null;

        //获得数据库连接,从连接池里获取
        //采用连接池能提高数据库性能
        conn= DbUtilHikari.getConnection();
        //定义并准备SQL语句,登录是查询语句
        String sql = "SELECT * from users where username=? and userpwd=?";
        try {
            pstmt = conn.prepareStatement(sql);
            //给问号赋值
            pstmt.setString(1,uname);
            pstmt.setString(2,SecureUtil.md5(pwd));
            //下达执行指令
            rs= pstmt.executeQuery();
            if(rs.next()){
                flag= true;
            }else{
                flag= false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtilHikari.close(rs,pstmt,conn);
        }

        return flag;
    }
    /**
     *
     * @param uname
     * @param upwd
     * @return
     */
    public int insertUser(String uname,String upwd){
        Connection conn= null;
        PreparedStatement pstmt= null;
        //注册成功与否标志，0，注册失败，1，注册成功
        int flag= 0;

        //获取数据库连接
        conn= DbUtilHikari.getConnection();

        String sql="INSERT INTO users(username,userpwd) VALUES(?,?)";

        try {
            pstmt= conn.prepareStatement(sql);

            pstmt.setString(1,uname);
            pstmt.setString(2, SecureUtil.md5(upwd));

            flag= pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtilHikari.close(pstmt,conn);
        }

        return flag;
    }
}